Pie chart on a calculated measure in DirectQuery mode

[trespass]

Hi All,

I'm trying to create a Pie chart using a calculated measure in DirectQuery mode (so, I'm not able to create a new  "Table" as suggested in previous post).

here my data:

STORE |  STORE VALUE  |   STORECLASS 

A                    100                       H

B                      90                       L 

C                    120                       H

"STOREVALUE" is a measure calculated as 'SUM(Table[money])'

"STORECLASS" is a measure calculated as 'IF(Table[STOREVALUE] >= 100 ,"H","L")'

I can't use STORECLASS as Legend and its count as Value (cannot use measure as legend), and I'm not able to find any alternatives than usi R code and embed it in ggplot2.

Is there a way to build a pie chart using calculated classes and their count?

 many thnaks and best regards,

Massimo

[v-huizhn-msft]

Hi @trespass,

You want tp display H(count:2) and L(count:1) in the pie chart, right? If my understanding is right, we can't bulid a pie chart using calculated classes(measure) and their count.

But there is an option, you can create a new table and create calculated column, add the column as legend.

1. Create a new table by clicking "New Table" under Modeling on Home page using the formula.

NewTable = SUMMARIZE('Table','Table'[STORE],"STOREVALUE",SUM('Table'[money]))

2. Then create a calculated column using the formula.

STORECLASS = IF(NewTable[STOREVALUE]>=100,"H","L")

3. Create a pie chart, select STORECLASS as Legend, and count of STORECLASS as value, you will get expected result as follows.



Best Regards,
Angelia

[trespass]

Hi Angelia,

thanks a lot for you support, but in Direct Query mode the "Table" button is disabled. I can't crate new tables using your code.

best regards,

Massimo

[v-huizhn-msft · Accepted solution]

Hi @trespass，

Ok, got it. You can use Group by in Power Query Editor, SUM('Table'[money]) group by 'Table'[STORE], click Apply. Then in Power BI desktop interface, you can create a calculated column STORECLASS. Finally, you will create a pie chart as described above.

Best Regards,
Angelia