Forum Discussion
Attributes value and mesure
Dear Team
I had belowe table which I did unppivot
| Date | Country | Gross Sale | Bonus | Net Sale | Cost of Sales | Gross Profit | Capacity Cost | Depreciation | Net Profit |
After unpivot I had all values in one column and respecivre key figure name in other column as below
| Date | Country | Key Figures | Value |
| xx | xx | Gross Sale | 1000 |
| xx | xx | Bonus | 10 |
| xx | xx | Net Sale | 990 |
| xx | xx | Cost of Sales | 500 |
| xx | xx | Gross Profit | 490 |
| xx | xx | Capacity Cost | 290 |
| xx | xx | Depreciation | 50 |
| xx | xx | Net Profit | 150 |
| xx | xx | xx |
Now I want make measures and pick particualr value from the key figures but I don't how to pick particualr key figures from the unpivot column. When I pick it, it gives full column name without specific value option
So let's say I want to calculate Gross profit % which is Gross Profit divide by Gross Sales
I want to know how to pick above two key figures in my measure
Please help
Hi,
Please try this:
Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Key Figures]="Gross Profit"))/CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Key Figures]="Gross Sale"))The result shows:
Best Regards,
Giotto Zhi
3 Replies
You can do something like this
divide(sumx(filter(table,table[Key Figures]="Gross Profit"),table[Value]),sumx(filter(table,table[Key Figures]="Gross Sale"),table[Value]))Measure slicer : https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
But better to unpivot : https://radacad.com/pivot-and-unpivot-with-power-bi
Hi,
Please try this:
Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Key Figures]="Gross Profit"))/CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Key Figures]="Gross Sale"))The result shows:
Best Regards,
Giotto Zhi
Hi,
Try this measure
Measure = CALCULATE(SUM('Table'[Value]),'Table'[Key Figures]="Gross Profit")/CALCULATE(SUM('Table'[Value]),'Table'[Key Figures]="Gross Sale")Hope this helps.
